from connect_mysql import StockDBOperator
import akshare as ak
import pandas as pd
import csv


def get_stock_data(name):
    try:
        # 获取中国平安（股票代码：601318）的日线数据
        stock_data = ak.stock_zh_a_hist(symbol=name, period="daily", start_date="20230101", end_date="20240101")
        # 将成交额按照 万为单位，并保留两位小数
        stock_data['成交额'] = stock_data['成交额'] / 10000/10000
        stock_data['成交额'] = stock_data['成交额'].round(2)

        # 将成交量按照 万为单位，并保留两位小数
        stock_data['成交量'] = stock_data['成交量'] / 10000
        stock_data['成交量'] = stock_data['成交量'].round(2)
        
        # 计算量比，过去5日平均成交量(a), 当日成交量 (b) , 量比 = b/a
        stock_data['量比'] = stock_data['成交量'] / stock_data['成交量'].rolling(window=5,closed='left').mean()
        stock_data = stock_data.fillna(0)
        # stock_data['量比'] = 1


        stock_data = stock_data.rename(columns={'成交量': '成交量(万手)'})
        stock_data = stock_data.rename(columns={'成交额': '成交额(亿)'})
        stock_data = stock_data.iloc[::-1].reset_index(drop=True)
        return stock_data
    except Exception as e:
        print(f"获取数据时出现错误: {e}")
        return None
    
    
def get_one_datas():
    # 从csv文件中获取id , name
    # 读取 CSV 文件
    file_path = 'stock_name.csv'
    
    result_code = []
    result_name = []
    
    try:
        with open(file_path, 'r', newline='', encoding='utf-8') as csvfile:
            reader = csv.reader(csvfile)
            for row in reader:
                if len(row) >= 3:
                    result_code.append(row[1])
                    result_name.append(row[2])
    except FileNotFoundError:
        print(f"错误: 文件 {file_path} 未找到。")
    except Exception as e:
        print(f"发生未知错误: {e}")
        

    mysql_handler = StockDBOperator(host='8.134.198.130',port=3306,user='root',password='A123@jiqiren',database='stocks_db')    
    mysql_handler.connect() 
    # 操作表
    mysql_handler.set_table_name('stocks2_info')
    
    # 创建表 
    create_table_sql = f"""
            CREATE TABLE IF NOT EXISTS stocks_info (
                id INT AUTO_INCREMENT PRIMARY KEY,
                日期 DATE,
                股票代码 VARCHAR(20),
                名称 VARCHAR(20),
                开盘 DECIMAL(10, 2),
                收盘 DECIMAL(10, 2),
                最高 DECIMAL(10, 2),
                最低 DECIMAL(10, 2),
                成交量 DECIMAL(10, 2),
                成交额 DECIMAL(10, 2),
                振幅 DECIMAL(10, 2),
                涨跌幅 DECIMAL(10, 2),
                涨跌额 DECIMAL(10, 2),
                换手率 DECIMAL(10, 2),
                量比 DECIMAL(10, 2)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            """
    mysql_handler.create_tables(create_table_sql)   
    
    # all_count = len(result_code) 
        
    # idx = 1
    # print(result_code[0],result_code[1])
    # while idx < all_count:                 
    #     # 第一次获取半年的数据  
    #     code = result_code[idx]      
    #     str_name = result_name[idx]
    #     print(f'code: {code},str_name: {str_name}')
    #     stock_df = get_stock_data(code)
                
    #     print(f'idx: {idx}, code: {code},str_name: {str_name}')        
    #     # print(stock_df)
        
    #     bret = mysql_handler.inser_df_data(str_name,code,stock_df)
    #     if not bret:
    #         break
        
    #     # mysql_handler.disconnect()
    #     idx+=1

def get_code_and_name():
    mysql_handler = StockDBOperator(host='8.134.198.130',port=3306,user='root',password='A123@jiqiren',database='stocks_db')    
    mysql_handler.connect() 
    sql2 = f"""
        CREATE TABLE IF NOT EXISTS stocks_name (
            id INT AUTO_INCREMENT PRIMARY KEY,
            股票代码 VARCHAR(20),
            名称 VARCHAR(20)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """
    mysql_handler.create_tables(sql2)  
    print('创建名称列表成功')
    # 获取 A 股列表数据
    all_stock_code_name = ak.stock_info_a_code_name()
    print('获取名称列表成功')

    # 打印前几行数据查看结果
    print(all_stock_code_name.head()) 
    # 插入数据
    mysql_handler.connect()
    for index, row in all_stock_code_name.iterrows():
        code = row['code']
        name = row['name']
        # 插入数据
        
        insert_sql = f"INSERT INTO stocks_name (股票代码, 名称) VALUES ('{code}', '{name}')"
        
        cursor = mysql_handler.cursor
        cursor.execute(insert_sql)

    # 提交事务
    mysql_handler.connection.commit()
    print("数据插入成功")

if __name__ == '__main__':    
    # get_one_datas()
    get_code_and_name()